﻿/* 
 * Since 2008 - 2012
 */

package com.hh.bss.customer.dao.impl;

import java.io.BufferedWriter;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.Writer;
import java.lang.reflect.InvocationTargetException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;
import java.util.*;

import com.hh.bss.base.*;
import com.hh.bss.common.ConstantDic;
import com.hh.bss.common.MapObjectRowMapper;
import com.hh.bss.common.page.*;
import com.hh.bss.common.page.impl.*;
import com.hh.bss.customer.dao.*;
import com.hh.bss.customer.entity.*;
import com.hh.bss.customer.service.*;
import com.hh.bss.customer.vo.query.*;
import com.hh.bss.talent.entity.Cv;
import com.hh.bss.util.*;



import static com.hh.bss.util.ObjectUtils.*;

import org.apache.commons.io.output.WriterOutputStream;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.hibernate.Hibernate;
import org.hibernate.Session;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

@Repository
public class CompanyDaoImpl extends BaseHibernateDao<Company, java.lang.Integer> implements CompanyDao<Company, java.lang.Integer> {

	public Class getEntityClass() {
		return Company.class;
	}

	public Page findPage(CompanyQuery query) {
		// XsqlBuilder syntax,please see
		// http://code.google.com/p/rapid-xsqlbuilder
		// [column]为字符串拼接, {column}为使用占位符.
		// [column]为使用字符串拼接,如username='[username]',偷懒时可以使用字符串拼接
		// [column] 为PageRequest的属性

		Class[] os = { CompanyQuery.class };
		Class property = org.springframework.beans.BeanUtils.findPropertyType(query.getField_type(), os);
		if (property != null) {
			if ("java.lang.String".equals(property.getName())) {

				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), query.getQuery());

			} else if ("java.lang.Integer".equals(property.getName())) {

				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), StringHelper.parseInteger(query.getQuery(), 0));

			} else if ("java.lang.Double".equals(property.getName())) {
				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), StringHelper.parseDouble(query.getQuery(), 0));
			} else if ("java.util.Date".equals(property.getName())) {
				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), DateHelper.toDate(query.getQuery(), DateHelper.DEFAULT_DATE_FORMAT));
			}
		}
		StringBuffer sql = new StringBuffer();
		List values = new ArrayList();
		sql.append(" select * from ( ");
		// sql.append(" select aa.*,c.contractId,d.custId  from ( ");
		// sql.append(" select a.id,c.contractId,if(c.expiredD >= now(),c.custId,NULL) custId ,a.name,a.abbr_name abbrName,a.full_name fullName,if(length(trim(a.name))=0,a.full_name,a.name) disName,a.size,a.industry,a.property,a.type,a.tel,a.website,a.fax,a.product,a.location,a.address,a.postcode,a.profile,a.culture,a.capital,a.integrity,a.background,a.creator,a.create_dt createDt,a.modifier from company a ");
		sql.append(" select a.id,a.name,a.abbr_name abbrName,a.full_name fullName,if(length(trim(a.name))=0,a.full_name,a.name) disName,a.size,a.industry,a.property,a.type,a.tel,a.website,a.fax,a.product,a.location,a.address,a.postcode,a.profile,a.culture,a.capital,a.integrity,a.background,a.creator,a.create_dt createDt,a.modifier,a.modified_dt modifiedDt,a.is_delete from company a ");
		sql.append(" where ifnull(a.integrity,'') <> '-1' ");
		// sql.append(" ) aa ");
		// sql.append(" left join (select a.company_id,a.id custId,max(b.id) contractId from customer a left join customer_contract b on a.id=b.cust_id group by a.company_id,a.id) c on aa.id=c.company_id ");
		// sql.append(" left join (select a.company_id,max(b.id) custId from customer a left join customer_bd b on a.id=b.cust_id group by a.company_id,a.id) d on aa.id=d.company_id ");
		// sql.append(" left join (select a.company_id,c.id custId,max(b.id) contractId from customer a left join customer_contract b on a.id=b.cust_id left join customer_bd c on a.id=c.cust_id where ifnull(b.end_d,now())>=now()  group by a.company_id,a.id) c on a.id=c.company_id ");
		// sql.append(" left join (select a.company_id,min(d.id) custId,max(ifnull(b.id,ifnull(c.contract_id,null))) contractId ,d.expired_d expiredD from customer a left join (select * from customer_bd a where ifnull(a.expired_d,now())>=now()) d on a.id=d.cust_id left join customer_contract b on a.id=b.cust_id left join customer_contract_service_company c on a.id=c.cust_id  group by a.company_id) c on a.id=c.company_id ");
		sql.append(" ) t where 1=1  ");

		if (isNotEmpty(query.getIsDelete())) {
			sql.append(" and t.is_delete=1 ");
		} else
			sql.append(" and t.is_delete=0 ");

		if (isNotEmpty(query.getId())) {
			sql.append(" and  t.id = ? ");
			values.add(query.getId());

		}
		if (isNotEmpty(query.getName())) {
			sql.append(" and  (t.name like ?  ");
			values.add("%" + query.getName() + "%");
			sql.append(" or  t.fullName like ?  ");
			values.add("%" + query.getName() + "%");
			sql.append(" or  t.abbrName like ?  ) ");
			values.add("%" + query.getName() + "%");

		}
		/*
		 * if(isNotEmpty(query.getFullName())) {
		 * sql.append(" and  t.fullName like ? ");
		 * values.add("%"+query.getFullName()+"%");
		 * 
		 * }
		 */
		if (isNotEmpty(query.getIndustry())) {
			sql.append(" and  t.industry = ? ");
			values.add(query.getIndustry());
		}
		// 一级行业
		if (isNotEmpty(query.getIndustryParent())) {
			// industryParent
			if (isEmpty(query.getIndustry())) {
				// sql.append(" and  exists( select 1 from system_dic a where t.industry = a.id  and a.category='INDUSTRY' and a.parent_id = "+query.getIndustrysParent()+" ) ");
				sql.append(" and  exists( select 1 from system_dic a where  1=1 and t.industry = a.id and a.parent_id =" + query.getIndustryParent() + ")");
			}
		}

		if (isNotEmpty(query.getProperty())) {
			sql.append(" and  t.property = ? ");
			values.add(query.getProperty());
		}
		if (isNotEmpty(query.getType())) {
			sql.append(" and  t.type = ? ");
			values.add(query.getType());
		}
		if (isNotEmpty(query.getTel())) {
			sql.append(" and  t.tel like ? ");
			values.add("%" + query.getTel() + "%");

		}
		if (isNotEmpty(query.getWebsite())) {
			sql.append(" and  t.website like ? ");
			values.add("%" + query.getWebsite() + "%");

		}
		if (isNotEmpty(query.getLocation())) {
			sql.append(" and  t.location = ? ");
			values.add(query.getLocation());

		}

		if (isNotEmpty(query.getAddress())) {
			sql.append(" and  t.address like ? ");
			values.add("%" + query.getAddress() + "%");

		}
		if (isNotEmpty(query.getPostcode())) {
			sql.append(" and  t.postcode like ? ");
			values.add("%" + query.getPostcode() + "%");

		}
		if (isNotEmpty(query.getProfile())) {
			sql.append(" and  t.profile like ? ");
			values.add("%" + query.getProfile() + "%");

		}
		if (isNotEmpty(query.getProduct())) {
			// product
			sql.append(" and  t.product like ? ");
			values.add("%" + query.getProduct() + "%");
		}
		if (isNotEmpty(query.getSize())) {
			// Size
			sql.append(" and  t.size = ? ");
			values.add(query.getSize());
		}
		if (isNotEmpty(query.getIntegrity())) {
			// integrity
			sql.append(" and  t.integrity = ? ");
			values.add(query.getIntegrity());
		}
		if (isNotEmpty(query.getExintegrity())) {
			sql.append(" and  not ifnull(t.integrity,0) in (").append(query.getExintegrity()).append(") ");
		}
		// 公司查询新添加关键字搜索，覆盖：网站(website)、地址(address)、邮编(postcode)、电话(tel)、公司背景(background)、公司文化(culture)、公司简介(profile)。
		if (isNotEmpty(query.getKeyword())) {
			// sql.append(" and  (t.website like ?  or t.address like ? or  t.postcode like ? or  t.tel like ? or t.background like ? )");
			sql.append(" and ( t.website like ? ");
			values.add("%" + query.getKeyword() + "%");
			sql.append(" or   t.address like ? ");
			values.add("%" + query.getKeyword() + "%");
			sql.append(" or   t.postcode like ? ");
			values.add("%" + query.getKeyword() + "%");
			sql.append(" or   t.tel like ? ");
			values.add("%" + query.getKeyword() + "%");
			sql.append(" or   t.background like ? ");
			values.add("%" + query.getKeyword() + "%");
			sql.append(" or   t.culture like ? ");
			values.add("%" + query.getKeyword() + "%");
			sql.append(" or   t.profile like ? ) ");
			values.add("%" + query.getKeyword() + "%");

		}
		if (isNotEmpty(query.getIsProtectCom())) {
			sql.append(" and   not exists(select 1 from  customer a where a.company_id=t.id ) ");

		}
		if (isNotEmpty(query.getCreator())) {
			sql.append(" and  t.creator = ? ");
			values.add(query.getCreator());
		}
		/** 排除创建人 */
		if (isNotEmpty(query.getExcreator())) {
			if (isEmpty(query.getCreator()))
				// sql.append(" and  t.creator not in (").append(query.getExcreator()).append(")");
				sql.append(" and  t.creator not in ( ? )");
			values.add(query.getExcreator());
		}
		if (isNotEmpty(query.getCreateDtBegin())) {
			sql.append(" and  t.createDt >= ? ");
			values.add(query.getCreateDtBegin());
		}
		if (isNotEmpty(query.getCreateDtEnd())) {
			sql.append(" and  t.createDt < ? ");
			values.add(DateHelper.add(query.getCreateDtEnd(), DateHelper.DAY, 1));
		}
		// .a.modifier modifiedDt
		if (isNotEmpty(query.getModifier())) {
			// product
			sql.append(" and  t.modifier = ? ");
			values.add(query.getModifier());
		}
		if (isNotEmpty(query.getModifiedDtBegin())) {
			// product
			sql.append(" and  t.modifiedDt >= ? ");
			values.add(query.getModifiedDtBegin());
		}
		if (isNotEmpty(query.getModifiedDtEnd())) {
			// product
			sql.append(" and  t.modifiedDt < ? ");
			values.add(DateHelper.add(query.getModifiedDtEnd(), DateHelper.DAY, 1));
		}

		if (isNotEmpty(query.getCompanyType())) {
			String s = query.getCompanyType();
			// BD客户和 签约客户
			if (s.contains(ConstantDic.COMPANY_BD) && s.contains(ConstantDic.COMPANY_CT)) {
				sql.append(" and  exists(select 1 from  customer a ,customer_bd  b where a.id=b.cust_id  and   a.company_id=t.id ) ");
			} else if (ConstantDic.COMPANY_BD.equals(s)) { // BD客户
				// BD种类 ---直接BD客户
				//sql.append(" and  exists(select 1 from  customer a ,customer_bd  b where ifnull(b.expired_d,now())>=now()  and a.id=b.cust_id   and not exists ( select 1 from customer_contract a1 where ifnull(a1.end_d, now()) >= now() and a1.cust_id = b.cust_id )   and   a.company_id=t.id  ) ");
				sql.append("and  exists ( select 1 from (select a.id,a.company_id from customer a where  ");
				sql.append(" not exists  (select 1 from (select  distinct  a.cust_id from  customer_contract a where ifnull(a.end_d,now()) >=now() union all  select  distinct  b.cust_id  from customer_contract_service_company b ,customer_contract c where ifnull(c.end_d,now()) >=now() and b.contract_id=c.id) t1 where  t1.cust_id = a.id )   ");
				sql.append(" and (exists (select 1 from customer_bd t where ifnull(t.expired_d,now()) >=now() and t.cust_id = a.id  ) ");
				sql.append(" or exists (select 1 from (select distinct t3.cust_id  from customer_contract_service_company  t3  where not exists (select 1 from customer_contract t4 where ifnull(t4.end_d,now()) >= now() and t4.id = t3.contract_id ) )	b where a.id =b.cust_id )  ");
				sql.append(" or exists (select 1 from customer_contract t7 where t7.end_d < now() and t7.cust_id = a.id ) ");
				sql.append(" ) ) a6 where a6.company_id =t.id ) " );
			} else if (ConstantDic.COMPANY_CT.equals(s)) { // 签约客户
				sql.append(" and  exists(select 1 from  (select b.company_id from customer b inner join (( select a.id, a.cust_id from customer_contract a where a.end_d > now() group by a.cust_id ) union all ( select b.contract_id, b.cust_id from customer_contract a, customer_contract_service_company b where a.end_d > now() and a.id = b.contract_id group by b.cust_id )) c on b.id = c.cust_id) a2 where a2.company_id=t.id ) ");
			} else if (ConstantDic.COMPANY_OD.equals(s)) {
				// 曾经签约客户 永远不过期
			}
		}
		if (isNotEmpty(query.getSortColumns())) {
			sql.append("order by t.").append(query.getSortColumns());
		} else {
			sql.append(" order by  t.id desc  ");
		}
		logger.info("公司查询SQL:" + sql);
		// values.add(id);
		return super.getJdbcDao().createPage(sql.toString(), values.toArray(), new MapObjectRowMapper(), query.getPageSize(), query.getPageNumber());
	}

	public Page findSimplePage(CompanyQuery query) {
		// XsqlBuilder syntax,please see
		// http://code.google.com/p/rapid-xsqlbuilder
		// [column]为字符串拼接, {column}为使用占位符.
		// [column]为使用字符串拼接,如username='[username]',偷懒时可以使用字符串拼接
		// [column] 为PageRequest的属性

		Class[] os = { CompanyQuery.class };
		Class property = org.springframework.beans.BeanUtils.findPropertyType(query.getField_type(), os);
		if (property != null) {
			if ("java.lang.String".equals(property.getName())) {

				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), query.getQuery());

			} else if ("java.lang.Integer".equals(property.getName())) {

				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), StringHelper.parseInteger(query.getQuery(), 0));

			} else if ("java.lang.Double".equals(property.getName())) {
				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), StringHelper.parseDouble(query.getQuery(), 0));
			} else if ("java.util.Date".equals(property.getName())) {
				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), DateHelper.toDate(query.getQuery(), DateHelper.DEFAULT_DATE_FORMAT));
			}
		}
		StringBuffer sql = new StringBuffer();
		List values = new ArrayList();
		sql.append(" select * from ( ");
		sql.append(" select a.id,a.name,a.abbr_name abbrName,a.full_name fullName,if(length(trim(a.name))=0,a.full_name,a.name) disName,a.size,a.industry,a.property,a.type,a.tel,a.website,a.fax,a.product,a.location,a.address,a.postcode,a.profile,a.culture,a.capital,a.integrity,a.background,a.creator,a.create_dt createDt,a.modifier,a.modified_dt modifiedDt,a.is_delete from company a ");
		sql.append(" ) t where 1=1  ");

		if (isNotEmpty(query.getIsDelete())) {
			sql.append(" and t.is_delete=1 ");
		} else
			sql.append(" and t.is_delete=0 ");

		if (isNotEmpty(query.getId())) {
			sql.append(" and  t.id = ? ");
			values.add(query.getId());

		}
		if (isNotEmpty(query.getName())) {
			sql.append(" and  (t.name like ?  ");
			values.add("%" + query.getName() + "%");
			sql.append(" or  t.fullName like ?  ");
			values.add("%" + query.getName() + "%");
			sql.append(" or  t.abbrName like ?  ) ");
			values.add("%" + query.getName() + "%");

		}

		// 公司查询新添加关键字搜索，覆盖：网站(website)、地址(address)、邮编(postcode)、电话(tel)、公司背景(background)、公司文化(culture)、公司简介(profile)。
		if (isNotEmpty(query.getKeyword())) {
			// sql.append(" and  (t.website like ?  or t.address like ? or  t.postcode like ? or  t.tel like ? or t.background like ? )");
			sql.append(" and (t.website like ? ");
			values.add("%" + query.getKeyword() + "%");
			sql.append(" or   t.address like ? ");
			values.add("%" + query.getKeyword() + "%");
			sql.append(" or   t.postcode like ? ");
			values.add("%" + query.getKeyword() + "%");
			sql.append(" or   t.tel like ? ");
			values.add("%" + query.getKeyword() + "%");
			sql.append(" or   t.background like ? ");
			values.add("%" + query.getKeyword() + "%");
			sql.append(" or   t.culture like ? ");
			values.add("%" + query.getKeyword() + "%");
			sql.append(" or   t.profile like ? ) ");
			values.add("%" + query.getKeyword() + "%");

		}

		if (isNotEmpty(query.getSortColumns())) {
			sql.append("order by t.").append(query.getSortColumns());
		} else {
			sql.append(" order by  t.id desc  ");
		}
		logger.info("公司查询SQL:" + sql);
		// values.add(id);
		return super.getJdbcDao().createPage(sql.toString(), values.toArray(), new MapObjectRowMapper(), query.getPageSize(), query.getPageNumber());
	}

	@Override
	public void flush() {
		super.flush();
	}

	public List findcv(Integer id) {
		// TODO Auto-generated method stub
		StringBuffer sql = new StringBuffer();
		List values = new ArrayList();

		sql.append(" select a.id from cv a where a.id =? and ( exists (select 1 from cv_education a1 where a.id=a1.cv_id) or  exists (select 1 from cv_certificate a2 where a.id=a2.cv_id) or  exists (select 1 from cv_trainning a3 where a.id=a3.cv_id) or  exists (select 1 from cv_work a4 where a.id=a4.cv_id) or  exists (select 1 from cv_attachment a5 where a.id=a5.cv_id)) ");

		values.add(id);
		return super.getJdbcDao().findByQuery(sql.toString(), values.toArray(), new MapObjectRowMapper());

	}

	@Override
	public void importCompanyFromExcel(String path) {
		StringBuffer info = new StringBuffer();
		// 公司中文名 公司英文名 公司简称 公司性质 二级行业 机构类型 电话总机 网站 城市 人员规模 公司介绍-内部使用 传真 地址 邮编
		// 公司产品 行业排名 注册资本 资本描述 公司背景 公司文化 公司介绍-外部使用
		// Map paramsMap = new HashMap();
		// 公司中文名 公司英文名 公司简称 公司性质 二级行业 机构类型 电话总机 网站 城市 人员规模 公司介绍-内部使用 传真 地址 邮编
		// 公司产品 行业排名 注 册资本 资本描述 公司背景 公司文化 公司介绍-外部使用
		String[] params = { "name", "fullName", "abbrName", "property", "industry", "type", "tel", "website", "location", "size", "profile", "fax", "address", "postcode", "product", "productLevel", "regCapital", "capital", "background", "culture", "jobProfile" };
		try {
			InputStream inputStream = new FileInputStream(path);
			// 创建对Excel工作簿文件的引用
			HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
			// 创建对工作表的引用。
			HSSFSheet sheet = workbook.getSheetAt(0);
			// 遍历所有单元格，读取单元格
			for (int i = 1; i <= sheet.getLastRowNum(); i++) {
				HSSFRow row = sheet.getRow(i);
				if (row != null) {
					Company company = new Company();
					company.setCreateDt(new Date());
					company.setCreator("waki.hu");
					for (int j = 0; j < row.getLastCellNum(); j++) {
						HSSFCell cell = row.getCell(j); // 获取每个单元格对象
						if (cell != null) {
							// 通过 row.getCell(j).toString() 获取单元格内容，
							if (BeanUtils.isExistsProperty(Company.class, params[j])) {
								// BeanUtils.setProperty(company, params[j],
								// cell.toString());
								if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { // 单元格内容为数字
									BeanUtils.setProperty(company, params[j], cell.getNumericCellValue());
								} else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
									BeanUtils.setProperty(company, params[j], cell.getStringCellValue().toString());
								}

							}

						}
					}
					List companyList = this.findCompanyList(company);
					if (companyList != null && companyList.size() > 0) {
						info.append("简称:" + company.getAbbrName());
						info.append("\r\n");
						for (int k = 0; k < companyList.size(); k++) {
							Map map = (Map) companyList.get(k);
							String attrName = (String) map.get("abbr_name");
							String tel = (String) map.get("tel");
							String address = (String) map.get("address");

							info.append("  重复公司Id:").append(map.get("id"));
							if (StringUtils.isNotEmpty(attrName) && StringUtils.isNotEmpty(company.getAbbrName())) {
								if (attrName.contains(company.getAbbrName())) {
									info.append("  重复简称:" + attrName);
								}
							}
							if (StringUtils.isNotEmpty(tel) && StringUtils.isNotEmpty(company.getTel())) {
								if (tel.contains(company.getTel())) {
									info.append("  重复电话:" + tel);
								}
							}
							if (StringUtils.isNotEmpty(address) && StringUtils.isNotEmpty(company.getAddress())) {
								logger.info("地址:" + address);
								logger.info("地址2:" + company.getAddress());
								if (address.contains(company.getAddress())) {
									info.append("  重复地址:" + address);
								}
							}
							info.append("\r\n");
						}
					} else {
						if (StringUtils.isNotEmpty(company.getAbbrName())) {

							logger.info("公司:" + company.getAbbrName() + "导入成功!");
							this.save(company);
							this.flush();
							info.append("公司:" + company.getAbbrName() + "---->Success! \r\n");
						}

					}
				}

			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		logger.info("导入成功....");
		logger.info("========开始生成导入报告======");
		// OutputStream outputStream = new Out
		// WriterOutputStream outputStream = new WriterOutputStream(new
		// FileInputStream("C:\\import.txt"));
		BufferedWriter bufferedWriter = null;
		try {
			bufferedWriter = new BufferedWriter(new FileWriter("C:\\importCompanyReport.txt"));
			bufferedWriter.write(info.toString());
			bufferedWriter.flush();
			// bufferedWriter.close();
			logger.info("========生成导入报告结束======");
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			if (bufferedWriter != null) {
				try {
					bufferedWriter.close();
				} catch (Exception e2) {
					e2.printStackTrace();
				}
			}
		}
	}

	/**
	 * 根据公司简称 判断公司是否重复
	 * 
	 * @param company
	 * @return 公司 list
	 */
	public List findCompanyList(Company company) {
		List values = new ArrayList();
		StringBuffer sql = new StringBuffer();
		if (company != null) {
			sql.append(" select a.id,a.abbr_name,a.tel,a.address from company a where a.is_delete=0 and  a.abbr_name like ? ");
			values.add("%" + company.getAbbrName() + "%");
			if (StringUtils.isNotEmpty(company.getTel())) {
				sql.append(" or a.tel like  ? ");
				values.add("%" + company.getTel() + "%");
			}
			if (StringUtils.isNotEmpty(company.getAddress())) {
				sql.append(" or a.address like ?  ");
				values.add(company.getAddress());
			}
		}
		return this.getJdbcDao().findByQuery(sql.toString(), values.toArray(), new MapObjectRowMapper());
	}

	@Override
	public Page findDelPage(CompanyQuery query) {
		StringBuffer sql = new StringBuffer();
		List values = new ArrayList();
		sql.append(" select * from ( ");
		sql.append("select a.id,a.abbr_name abbrName,a.name,a.full_name fullName,a.location,a.address,a.tel,a.integrity,a.is_delete isDelete,delete_dt deleteDt,a.modifier,a.modified_dt modifiedDt ");
		sql.append(" from company a where 1=1 and (a.integrity =-1 or a.is_delete = 1 )");

		sql.append(" ) t where 1=1  ");

		if (isNotEmpty(query.getId())) {
			sql.append(" and  t.id = ? ");
			values.add(query.getId());

		}
		if (isNotEmpty(query.getName())) {
			sql.append(" and  (t.name like ?  ");
			values.add("%" + query.getName() + "%");
			sql.append(" or  t.fullName like ?  ");
			values.add("%" + query.getName() + "%");
			sql.append(" or  t.abbrName like ?  ) ");
			values.add("%" + query.getName() + "%");

		}
		if (isNotEmpty(query.getIsDelete())) {
			sql.append(" and t.isDelete= ? ");
			values.add(query.getIsDelete());
		}

		if (isNotEmpty(query.getIndustry())) {
			sql.append(" and  t.industry = ? ");
			values.add(query.getIndustry());
		}
		if (isNotEmpty(query.getTel())) {
			sql.append(" and  t.tel like ? ");
			values.add("%" + query.getTel() + "%");

		}
		if (isNotEmpty(query.getLocation())) {
			sql.append(" and  t.location = ? ");
			values.add(query.getLocation());

		}

		if (isNotEmpty(query.getAddress())) {
			sql.append(" and  t.address like ? ");
			values.add("%" + query.getAddress() + "%");

		}

		if (isNotEmpty(query.getSortColumns())) {
			sql.append("order by t.").append(query.getSortColumns());
		} else {
			sql.append(" order by  t.modifiedDt desc  ");
		}

		return super.getJdbcDao().createPage(sql.toString(), values.toArray(), new MapObjectRowMapper(), query.getPageSize(), query.getPageNumber());
	}

	@Override
	public synchronized void companyMerger(String id, String ids) {
		Company company = (Company) this.getById(Integer.valueOf(id));
		String[] idArr = ids.split(",");
		for (String companyId : idArr) {
			if (StringUtils.isNotEmpty(companyId)) {
				Company c = this.getById(Integer.valueOf(companyId));
				try {
					if (company != null && c != null) {
						BeanUtils.copyPropertiesToNull(c, company);
					}
				} catch (IllegalAccessException e) {
					e.printStackTrace();
				} catch (InvocationTargetException e) {
					e.printStackTrace();
				}
			}
			this.insertCompanyMerger(id, company.getAbbrName(), ids); // 插入记录
			company.setIntegrity("10");
			this.update(company); // 合并基本信息
			this.prepareCall(id, ids); // 调用存储过程
		}
	}

	private void prepareCall(final String id, final String ids) {

		final String callProcedureSql = "{call company_merge(?,?)}"; // 定义sql
		List<SqlParameter> params = new ArrayList<SqlParameter>();
		params.add(new SqlParameter("id", Types.INTEGER));
		params.add(new SqlParameter("ids", Types.VARCHAR));

		Map<String, Object> outValues = this.getJdbcDao().getJdbcTemplate().call(new CallableStatementCreator() {

			@Override
			public CallableStatement createCallableStatement(Connection conn) throws SQLException {
				CallableStatement cstmt = conn.prepareCall(callProcedureSql);
				cstmt.setInt(1, Integer.valueOf(id));
				cstmt.setString(2, ids);

				return cstmt;
			}
		}, params);

	}

	private void insertCompanyMerger(String id, String attrName, String ids) {
		StringBuffer sql = new StringBuffer();
		List values = new ArrayList();
		sql.append("insert into company_merge_log values (null,?,?,?,?,?) ");
		values.add(id);
		values.add(attrName);
		values.add(ids);
		values.add("alvin.liu");
		values.add(new Date());
		this.getJdbcDao().save(sql.toString(), values.toArray());

	}

	@Override
	public Page findCompanyMergePage(CompanyQuery query) {
		Class[] os = { CompanyQuery.class };
		Class property = org.springframework.beans.BeanUtils.findPropertyType(query.getField_type(), os);
		if (property != null) {
			if ("java.lang.String".equals(property.getName())) {

				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), query.getQuery());

			} else if ("java.lang.Integer".equals(property.getName())) {

				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), StringHelper.parseInteger(query.getQuery(), 0));

			} else if ("java.lang.Double".equals(property.getName())) {
				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), StringHelper.parseDouble(query.getQuery(), 0));
			} else if ("java.util.Date".equals(property.getName())) {
				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), DateHelper.toDate(query.getQuery(), DateHelper.DEFAULT_DATE_FORMAT));
			}
		}
		StringBuffer sql = new StringBuffer();
		List values = new ArrayList();
		sql.append(" select * from ( ");
		sql.append(" select a.id,a.name,a.abbr_name abbrName,a.full_name fullName,if(length(trim(a.name))=0,a.full_name,a.name) disName,a.size,a.industry,a.property,a.type,a.tel,a.website,a.fax,a.product,a.location,a.address,a.postcode,a.profile,a.culture,a.capital,a.integrity,a.background,a.creator,a.create_dt createDt,a.modifier,a.modified_dt modifiedDt,a.is_delete from company a ");
		sql.append(" ) t where 1=1  ");

		if (isNotEmpty(query.getIsDelete())) {
			sql.append(" and t.is_delete=1 ");
		} else
			sql.append(" and t.is_delete=0 ");

		if (isNotEmpty(query.getId())) {
			sql.append(" and  t.id = ? ");
			values.add(query.getId());

		}
		if (isNotEmpty(query.getName())) {
			sql.append(" and  (t.name like ?  ");
			values.add("%" + query.getName() + "%");
			sql.append(" or  t.fullName like ?  ");
			values.add("%" + query.getName() + "%");
			sql.append(" or  t.abbrName like ?  ) ");
			values.add("%" + query.getName() + "%");

		}
		if (isNotEmpty(query.getIndustry())) {
			sql.append(" and  t.industry = ? ");
			values.add(query.getIndustry());
		}
		// 一级行业
		if (isNotEmpty(query.getIndustryParent())) {
			// industryParent
			if (isEmpty(query.getIndustry())) {
				// sql.append(" and  exists( select 1 from system_dic a where t.industry = a.id  and a.category='INDUSTRY' and a.parent_id = "+query.getIndustrysParent()+" ) ");
				sql.append(" and  exists( select 1 from system_dic a where  1=1 and t.industry = a.id and a.parent_id =" + query.getIndustryParent() + ")");
			}
		}

		if (isNotEmpty(query.getProperty())) {
			sql.append(" and  t.property = ? ");
			values.add(query.getProperty());
		}
		if (isNotEmpty(query.getType())) {
			sql.append(" and  t.type = ? ");
			values.add(query.getType());
		}
		if (isNotEmpty(query.getTel())) {
			sql.append(" and  t.tel like ? ");
			values.add("%" + query.getTel() + "%");

		}
		if (isNotEmpty(query.getWebsite())) {
			sql.append(" and  t.website like ? ");
			values.add("%" + query.getWebsite() + "%");

		}
		if (isNotEmpty(query.getLocation())) {
			sql.append(" and  t.location = ? ");
			values.add(query.getLocation());

		}

		if (isNotEmpty(query.getAddress())) {
			sql.append(" and  t.address like ? ");
			values.add("%" + query.getAddress() + "%");

		}
		if (isNotEmpty(query.getPostcode())) {
			sql.append(" and  t.postcode like ? ");
			values.add("%" + query.getPostcode() + "%");

		}
		if (isNotEmpty(query.getProfile())) {
			sql.append(" and  t.profile like ? ");
			values.add("%" + query.getProfile() + "%");

		}
		if (isNotEmpty(query.getProduct())) {
			// product
			sql.append(" and  t.product like ? ");
			values.add("%" + query.getProduct() + "%");
		}
		if (isNotEmpty(query.getSize())) {
			// Size
			sql.append(" and  t.size = ? ");
			values.add(query.getSize());
		}
		if (isNotEmpty(query.getIntegrity())) {
			// integrity
			sql.append(" and  t.integrity = ? ");
			values.add(query.getIntegrity());
		}
		if (isNotEmpty(query.getExintegrity())) {
			sql.append(" and  not ifnull(t.integrity,0) in (").append(query.getExintegrity()).append(") ");
		}
		// 公司查询新添加关键字搜索，覆盖：网站(website)、地址(address)、邮编(postcode)、电话(tel)、公司背景(background)、公司文化(culture)、公司简介(profile)。
		if (isNotEmpty(query.getKeyword())) {
			// sql.append(" and  (t.website like ?  or t.address like ? or  t.postcode like ? or  t.tel like ? or t.background like ? )");
			sql.append(" and t.website like ? ");
			values.add("%" + query.getKeyword() + "%");
			sql.append(" or   t.address like ? ");
			values.add("%" + query.getKeyword() + "%");
			sql.append(" or   t.postcode like ? ");
			values.add("%" + query.getKeyword() + "%");
			sql.append(" or   t.tel like ? ");
			values.add("%" + query.getKeyword() + "%");
			sql.append(" or   t.background like ? ");
			values.add("%" + query.getKeyword() + "%");
			sql.append(" or   t.culture like ? ");
			values.add("%" + query.getKeyword() + "%");
			sql.append(" or   t.profile like ? ");
			values.add("%" + query.getKeyword() + "%");

		}
		if (isNotEmpty(query.getIsProtectCom())) {
			sql.append(" and   not exists(select 1 from  customer a where a.company_id=t.id ) ");

		}
		if (isNotEmpty(query.getCreator())) {
			sql.append(" and  t.creator = ? ");
			values.add(query.getCreator());
		}
		/** 排除创建人 */
		if (isNotEmpty(query.getExcreator())) {
			if (isEmpty(query.getCreator()))
				// sql.append(" and  t.creator not in (").append(query.getExcreator()).append(")");
				sql.append(" and  t.creator not in ( ? )");
			values.add(query.getExcreator());
		}
		if (isNotEmpty(query.getCreateDtBegin())) {
			sql.append(" and  t.createDt >= ? ");
			values.add(query.getCreateDtBegin());
		}
		if (isNotEmpty(query.getCreateDtEnd())) {
			sql.append(" and  t.createDt < ? ");
			values.add(DateHelper.add(query.getCreateDtEnd(), DateHelper.DAY, 1));
		}
		// .a.modifier modifiedDt
		if (isNotEmpty(query.getModifier())) {
			// product
			sql.append(" and  t.modifier = ? ");
			values.add(query.getModifier());
		}
		if (isNotEmpty(query.getModifiedDtBegin())) {
			// product
			sql.append(" and  t.modifiedDt >= ? ");
			values.add(query.getModifiedDtBegin());
		}
		if (isNotEmpty(query.getModifiedDtEnd())) {
			// product
			sql.append(" and  t.modifiedDt < ? ");
			values.add(DateHelper.add(query.getModifiedDtEnd(), DateHelper.DAY, 1));
		}

		if (isNotEmpty(query.getCompanyType())) {
			String s = query.getCompanyType();
			// BD客户和 签约客户
			if (s.contains(ConstantDic.COMPANY_BD) && s.contains(ConstantDic.COMPANY_CT)) {
				sql.append(" and  exists(select 1 from  customer a ,customer_bd  b where a.id=b.cust_id  and   a.company_id=t.id ) ");
			} else if (ConstantDic.COMPANY_BD.equals(s)) { // BD客户
				sql.append(" and  exists(select 1 from  (select b.id,b.company_id from customer_bd a,customer b where a.cust_id=b.id and ifnull(a.expired_d,now())>=now()) a1 where a1.company_id=t.id and  not exists (select 1 from (( select a.id, a.cust_id from customer_contract a where a.end_d > now() group by a.cust_id ) union all (select b.contract_id, b.cust_id from customer_contract a, customer_contract_service_company b where a.end_d > now() and a.id = b.contract_id group by b.cust_id )) a2 where a1.id = a2.cust_id )) ");
			} else if (ConstantDic.COMPANY_CT.equals(s)) { // 签约客户
				sql.append(" and  exists(select 1 from  (select b.company_id from customer b inner join (( select a.id, a.cust_id from customer_contract a where a.end_d > now() group by a.cust_id ) union all ( select b.contract_id, b.cust_id from customer_contract a, customer_contract_service_company b where a.end_d > now() and a.id = b.contract_id group by b.cust_id )) c on b.id = c.cust_id) a2 where a2.company_id=t.id ) ");
			}
		}
		if (isNotEmpty(query.getSortColumns())) {
			sql.append("order by t.").append(query.getSortColumns());
		} else {
			sql.append(" order by  t.id desc  ");
		}
		logger.info("公司查询SQL:" + sql);
		return super.getJdbcDao().createPage(sql.toString(), values.toArray(), new MapObjectRowMapper(), query.getPageSize(), query.getPageNumber());
	}

	@Override
	public Page findSameOfDelCvPage(CompanyQuery companyQuery) {
		List values = new ArrayList();
		StringBuffer sql = new StringBuffer();
		sql.append("select * from ( ");
		sql.append(" select a.id,a.abbr_name abbrName,a.name,a.full_name fullName,a.integrity,a.tel,a.address,a.location,a.is_delete isdelete,a.modifier,a.modified_dt  modifiedDt");
		sql.append(" from company a where 1=1 and a.id <> ?  ");
		values.add(companyQuery.getId());
		if (isNotEmpty(companyQuery.getName()) && isNotEmpty(companyQuery.getFullName())) {
			sql.append(" and  (a.name = ? or a.full_name = ? )");
			values.add(companyQuery.getName());
			values.add(companyQuery.getFullName());
		} else if (isNotEmpty(companyQuery.getName()) && !isNotEmpty(companyQuery.getFullName())) {
			sql.append(" and a.name = ? ");
			values.add(companyQuery.getName());
		} else if (!isNotEmpty(companyQuery.getName()) && isNotEmpty(companyQuery.getFullName())) {
			sql.append(" and a.full_name = ? ");
			values.add(companyQuery.getFullName());
		}
		sql.append(" ) t where 1=1 ");
		return super.getJdbcDao().createPage(sql.toString(), values.toArray(), new MapObjectRowMapper(), companyQuery.getPageSize(), companyQuery.getPageNumber());
	}

	@Override
	public boolean checkCompanyIntegrity(Integer companyid) {
		List integrityList = new ArrayList();
		integrityList.add("-1"); // 申请删除
		List values = new ArrayList();
		StringBuffer sql = new StringBuffer();
		sql.append(" select a.id,a.integrity  ");
		sql.append(" from company a where 1=1 and a.id =  ?  ");
		values.add(companyid);
		List l = this.getJdbcDao().findByQuery(sql.toString(), values.toArray(), new MapObjectRowMapper());
		if (l != null && l.size() > 0) {
			Map m = (Map) l.get(0);
			String integrity = (String) m.get("integrity");
			if (isNotEmpty(integrity)) {
				if (integrityList.contains(integrity)) {
					return false;
				}
			}

		}
		return true;
	}

	@Override
	public void recoverCompany(String ids) {
		StringBuffer sql = new StringBuffer();
		List values = new ArrayList();
		sql.append(" update company a set a.integrity = null ,a.is_delete = 0 ,a.delete_dt = null where a.is_delete <> 0 and find_in_set(a.id,?) ");
		values.add(ids);
		this.getJdbcDao().update(sql.toString(), values.toArray());
	}

}
